﻿using System;
using System.Linq;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.Metadata.Entity;
using T.FAS.Runtime.DataModel.Dao;
using T.FAS.Runtime.Metadata.Entity.DataModel;

namespace T.FAS.Runtime.DataModel.Dao
{
    public class DataTypeTool
    {
        private static XMLHelper s_xmlHelper;
        private static Dictionary<string, string> s_typeMapping;
        private static Dictionary<string, string> s_databaseTypeMapping;

		private const string dataTypeXMLContentString =
@"<?xml version='1.0' encoding='utf-8'?>
<Root xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='DataType.xsd'>
	<DataTypeDefined>
		<DBMSList>
			<DBMSItem ID='SQLServer' Name='SQL Server'/>
			<DBMSItem ID='MySQL' Name='MySQL'/>
			<DBMSItem ID='PostgreSQL' Name='PostgreSQL'/>
		</DBMSList>
		<BaseTypeList>
			<BaseItem ID='bit' Code='bit' Name='比特'/>
			<BaseItem ID='tinyint' Code='tinyint' Name='微整型'/>
			<BaseItem ID='smallint' Code='smallint' Name='小整型'/>
			<BaseItem ID='int' Code='int' Name='整型'/>
			<BaseItem ID='bigint' Code='bigint' Name='长整型'/>
			<BaseItem ID='decimal' Code='decimal' Name='精确数值类型'/>
			<BaseItem ID='datetime' Code='datetime' Name='日期类型'/>
			<BaseItem ID='timestamp' Code='timestamp' Name='时间戳类型'/>
			<BaseItem ID='date' Code='date' Name='日期'/>
			<BaseItem ID='time' Code='time' Name='时间'/>
			<BaseItem ID='nchar' Code='nchar' Name='unicode字符串'/>
			<BaseItem ID='nvarchar' Code='nvarchar' Name='unicode变长字符串'/>
			<BaseItem ID='nvarchar(max)' Code='nvarchar(max)' Name='unicode变长字符(max)'/>
			<BaseItem ID='char' Code='char' Name='字符串'/>
			<BaseItem ID='varchar' Code='varchar' Name='变长字符串'/>
			<BaseItem ID='varchar(max)' Code='varchar(max)' Name='变长字符(max)'/>
			<BaseItem ID='binary' Code='binary' Name='二进制类型'/>
			<BaseItem ID='varbinary' Code='varbinary' Name='二进制类型'/>
			<BaseItem ID='varbinary(max)' Code='varbinary(max)' Name='二进制类型(max)'/>
		</BaseTypeList>
		<CustomTypeList>
			<CustomItem ID='utid' Code='utid' Name='标识符'>
				<Length>36</Length>
				<BaseType>varchar</BaseType>
			</CustomItem>
			<CustomItem ID='utcode' Code='utcode' Name='编号'>
				<Length>36</Length>
				<BaseType>varchar</BaseType>
			</CustomItem>
			<CustomItem ID='utname' Code='utname' Name='名称'>
				<Length>256</Length>
				<BaseType>varchar</BaseType>
			</CustomItem>
			<CustomItem ID='utnode' Code='utnode' Name='备注'>
				<Length>1024</Length>
				<BaseType>varchar</BaseType>
			</CustomItem>
			<CustomItem ID='utdate' Code='utdate' Name='短日期'>
				<Length>8</Length>
				<BaseType>varchar</BaseType>
			</CustomItem>
			<CustomItem ID='utdatetime' Code='utdatetime' Name='长日期'>
				<BaseType>datetime</BaseType>
			</CustomItem>
			<CustomItem ID='utmoney' Code='utmoney' Name='金额'>
				<Length>20</Length>
				<Precision>8</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
			<CustomItem ID='utprice' Code='utprice' Name='单价'>
				<Length>20</Length>
				<Precision>8</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
			<CustomItem ID='utproportion' Code='utproportion' Name='比例'>
				<Length>7</Length>
				<Precision>4</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
			<CustomItem ID='utquantity' Code='utquantity' Name='数量'>
				<Length>20</Length>
				<Precision>8</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
			<CustomItem ID='utrate' Code='utrate' Name='百分比'>
				<Length>10</Length>
				<Precision>6</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
			<CustomItem ID='uttaxrate' Code='uttaxrate' Name='税率'>
				<Length>9</Length>
				<Precision>6</Precision>
				<BaseType>decimal</BaseType>
			</CustomItem>
		</CustomTypeList>
		<MapList DBMSItem='SQLServer'>
			<MapItem>
				<BaseType>bit</BaseType>
				<PhysicalType>bit</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>tinyint</BaseType>
				<PhysicalType>tinyint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>smallint</BaseType>
				<PhysicalType>smallint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>int</BaseType>
				<PhysicalType>int</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>bigint</BaseType>
				<PhysicalType>bigint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>decimal</BaseType>
				<PhysicalType>decimal</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>datetime</BaseType>
				<PhysicalType>datetime</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>timestamp</BaseType>
				<PhysicalType>datetime</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>date</BaseType>
				<PhysicalType>date</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>time</BaseType>
				<PhysicalType>time</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nchar</BaseType>
				<PhysicalType>nchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar</BaseType>
				<PhysicalType>nvarchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar(max)</BaseType>
				<PhysicalType>nvarchar(max)</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>char</BaseType>
				<PhysicalType>char</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar</BaseType>
				<PhysicalType>varchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar(max)</BaseType>
				<PhysicalType>varchar(max)</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>binary</BaseType>
				<PhysicalType>binary</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary</BaseType>
				<PhysicalType>varbinary</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary(max)</BaseType>
				<PhysicalType>varbinary(max)</PhysicalType>
			</MapItem>
		</MapList>
		<MapList DBMSItem='MySQL'>
			<MapItem>
				<BaseType>bit</BaseType>
				<PhysicalType>tinyint(1)</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>tinyint</BaseType>
				<PhysicalType>tinyint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>smallint</BaseType>
				<PhysicalType>smallint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>int</BaseType>
				<PhysicalType>int</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>bigint</BaseType>
				<PhysicalType>bigint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>decimal</BaseType>
				<PhysicalType>decimal</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>datetime</BaseType>
				<PhysicalType>datetime</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>timestamp</BaseType>
				<PhysicalType>datetime</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>date</BaseType>
				<PhysicalType>date</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>time</BaseType>
				<PhysicalType>time</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nchar</BaseType>
				<PhysicalType>char</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar</BaseType>
				<PhysicalType>varchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar(max)</BaseType>
				<PhysicalType>longtext</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>char</BaseType>
				<PhysicalType>char</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar</BaseType>
				<PhysicalType>varchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar(max)</BaseType>
				<PhysicalType>longtext</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>binary</BaseType>
				<PhysicalType>binary</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary</BaseType>
				<PhysicalType>varbinary</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary(max)</BaseType>
				<PhysicalType>longblob</PhysicalType>
			</MapItem>
		</MapList>
		<MapList DBMSItem='PostgreSQL'>
			<MapItem>
				<BaseType>bit</BaseType>
				<PhysicalType>bit</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>tinyint</BaseType>
				<PhysicalType>smallint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>smallint</BaseType>
				<PhysicalType>smallint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>int</BaseType>
				<PhysicalType>int</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>bigint</BaseType>
				<PhysicalType>bigint</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>decimal</BaseType>
				<PhysicalType>decimal</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>datetime</BaseType>
				<PhysicalType>timestamp</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>timestamp</BaseType>
				<PhysicalType>timestamp</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>date</BaseType>
				<PhysicalType>date</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>time</BaseType>
				<PhysicalType>time</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nchar</BaseType>
				<PhysicalType>char</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar</BaseType>
				<PhysicalType>varchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>nvarchar(max)</BaseType>
				<PhysicalType>text</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>char</BaseType>
				<PhysicalType>char</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar</BaseType>
				<PhysicalType>varchar</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varchar(max)</BaseType>
				<PhysicalType>text</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>binary</BaseType>
				<PhysicalType>bytea</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary</BaseType>
				<PhysicalType>bytea</PhysicalType>
			</MapItem>
			<MapItem>
				<BaseType>varbinary(max)</BaseType>
				<PhysicalType>bytea</PhysicalType>
			</MapItem>
		</MapList>
	</DataTypeDefined>
</Root>";

        /// <summary>
        /// 不需要指定列宽的类型
        /// </summary>
        public readonly static HashSet<string> NotUseColumnWidthType = new HashSet<string>
        {
            DatabaseColumnType.Bit.ToString().ToLower(),
            DatabaseColumnType.TinyInt.ToString().ToLower(),
            DatabaseColumnType.SmallInt.ToString().ToLower(),
            DatabaseColumnType.Int.ToString().ToLower(),
            DatabaseColumnType.BigInt.ToString().ToLower(),
            //DatabaseColumnType.Decimal.ToString().ToLower(), //Decimal使用精度设置
            //DatabaseColumnType.Timestamp.ToString().ToLower(),
            //DatabaseColumnType.DateTime.ToString().ToLower(),
            DatabaseColumnType.Date.ToString().ToLower(),
            DatabaseColumnType.Time.ToString().ToLower(),
            "longtext", //mysql
			"text",		//mysql,pg
            "bytea",	//pg
        };

        static DataTypeTool()
        {
            //var xmlContent = File.ReadAllText("MetadataReleaseManager\\DataTypes.xml");
            var xmlContent = dataTypeXMLContentString;
            s_xmlHelper = new XMLHelper(xmlContent);
        }

        /// <summary>
        /// 返回目标数据库映射的实际类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetMappingDataType(DatabaseType dbType, string type)
        {
            string xpath = $"//MapList[@DBMSItem='{dbType}']/MapItem[BaseType/text()='{type.ToLower()}']//PhysicalType";
            return s_xmlHelper.GetSingleNodeContent(xpath);
        }

        /// <summary>
        /// 返回目标数据库映射的实际类型，特殊处理非配置的类型，比如SQLServer的NVARCHAR
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetProcessedMappingDataType(DatabaseType dbType, string type, DataColumn dataColumn)
        {
            string dbColumnType = GetMappingDataType(dbType, type);
            if (dbType == DatabaseType.SQLServer)
            {
                if (dataColumn.EnableUnicode && string.Equals(dbColumnType, "varchar", StringComparison.OrdinalIgnoreCase))
                {
                    return "nvarchar";
                }
            }
            return dbColumnType;
        }

        /// <summary>
        /// 获取目标数据库的映射类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static string GetMappingDataType(DatabaseType dbType, DataColumn column)
        {
            //将Do.Column的数据类型映射到目标类型数据库中，并且处理好精度 长度。

            string datatype;
            DataColumn fkCol = null;
            //数据类型为DO的列，需要取出外键DO的主键信息
            if (!string.IsNullOrEmpty(column.RelatedDataObjectID))
            {
                var fkObj = new DataObjectLogic().GetDataObjectByID(column.RelatedDataObjectID);
                if (fkObj == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象:{column.RelatedDataObjectID}不存在!");
                fkCol = fkObj.Columns.First(c => c.IsPkColumn);
                if (fkCol == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象不存在主键列!");
                datatype = GetProcessedMappingDataType(dbType, fkCol.DataType, column);
            }
            else
                datatype = GetProcessedMappingDataType(dbType, column.DataType, column);
            if (!datatype.Contains("("))
            {
                if (!NotUseColumnWidthType.Contains(datatype.ToLower()))
                {
                    ////特殊处理decimal
                    //if (column.DataType.ToLower() == "decimal")
                    //    datatype += $"({column.Length},{column.Precision})";
                    //else
                    //    datatype += $"({column.Length}{(column.Precision > 0 ? "," + column.Precision : "")})";

                    int length = fkCol == null ? column.Length : fkCol.Length;
                    int precision = fkCol == null ? column.Precision : fkCol.Precision;
                    datatype = GetAccurateDataType(dbType, datatype, length, precision);
                }
            }

            return datatype;
        }


        /// <summary>
        /// 一键调测时，获取目标数据库的映射类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="column"></param>
        /// <param name="dataObjects">一键调测时，同批次调测的DO集合</param>
        /// <returns></returns>
        public static string GetMappingDataType(DatabaseType dbType, DataColumn column, IList<DataObject> dataObjects)
        {
            //将Do.Column的数据类型映射到目标类型数据库中，并且处理好精度 长度。

            string datatype;
            DataColumn fkCol = null;
            //数据类型为DO的列，需要取出外键DO的主键信息
            if (!string.IsNullOrEmpty(column.RelatedDataObjectID))
            {
                var fkObj = dataObjects?.FirstOrDefault(@do => @do.ID == column.RelatedDataObjectID);    //Step 1: 在一键调测同批次调测的DO集合中查找
                if (fkObj == null)
                    fkObj = new DataObjectLogic().GetDataObjectByID(column.RelatedDataObjectID);    //在当前DB中查找
                if (fkObj == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象:{column.RelatedDataObjectID}不存在!");
                fkCol = fkObj.Columns.FirstOrDefault(c => c.IsPkColumn);
                if(fkCol == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象不存在主键列!");
                datatype = GetProcessedMappingDataType(dbType, fkCol.DataType, column);
            }
            else
                datatype = GetProcessedMappingDataType(dbType, column.DataType, column);
            if (string.IsNullOrEmpty(datatype))
            {
                throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的精准类型:{column.DataType}不正确请检查!");
            }
            if (!datatype.Contains("("))
            {
                if (!NotUseColumnWidthType.Contains(datatype.ToLower()))
                {
                    //if (column.DataType.ToLower() == "decimal") 
                    //    datatype += $"({column.Length},{column.Precision})";
                    //else //其余类型都指定列宽与精度
                    //    datatype += $"({column.Length}{(column.Precision > 0 ? "," + column.Precision : "")})";

                    int length = fkCol == null ? column.Length : fkCol.Length;
                    int precision = fkCol == null ? column.Precision : fkCol.Precision;
                    datatype = GetAccurateDataType(dbType, datatype, length, precision);
                }
            }

            return datatype;
        }

        /// <summary>
        /// 将DataColumn.ApplicationDataType 转换为 CNO.Element.DataType
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static DataType MappingDataColumnDataTypeToCNOElementDataType(DataColumn column, IList<DataObject> dataObjects = null)
        {
            //将Do.Column的数据类型映射到目标类型数据库中，并且处理好精度 长度。

            string datatype;
            //数据类型为DO的列，需要取出外键DO的主键信息
            if (!string.IsNullOrEmpty(column.RelatedDataObjectID))
            {
                var fkObj = dataObjects?.FirstOrDefault(@do => @do.ID == column.RelatedDataObjectID);    //Step 1: 在一键调测同批次调测的DO集合中查找
                if (fkObj == null)
                    fkObj = new DataObjectLogic().GetDataObjectByID(column.RelatedDataObjectID);         //Step 2: 在运行时DB中查询
                if (fkObj == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象:{column.RelatedDataObjectID}不存在!");
                var fkCol = fkObj.Columns.FirstOrDefault(c => c.IsPkColumn);
                if (fkCol == null)
                    throw new FasException(DataModelErrorCode.DataType.ForeignKeyError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的外键关联数据对象:{fkObj.Code}不存在主键列!");
                datatype = fkCol.ApplicationDataType;
            }
            else
                datatype = column.ApplicationDataType;

            if(Enum.TryParse(datatype, out DataType dataType))
            {
                return dataType;
            }
            else
            {
                throw new FasException(DataModelErrorCode.DataType.TypeCastError, $"数据对象:{column.DataObjectID}的列:{column.ColumnName}的数据类型:{column.ApplicationDataType}转换应用程序DataType失败");
            }
        }

        public static DataType ConvertToDataType(string datatype)
        {
            if (Enum.TryParse(datatype, out DataType dataType))
            {
                return dataType;
            }
            else
            {
                throw new FasException(DataModelErrorCode.DataType.TypeCastError, $"数据类型字符串:{datatype}转换应用程序DataType失败");
            }
        }

        public static DatabaseColumnType GetDefaultDatabaseColumnDataType(DataType datatype)
        {
            DatabaseColumnType databaseColumnType;
            switch (datatype)
            {
                case DataType.Binary:
                    databaseColumnType = DatabaseColumnType.Binary;
                    break;
                case DataType.Boolean:
                    databaseColumnType = DatabaseColumnType.Bit;
                    break;
                case DataType.Int:
                    databaseColumnType = DatabaseColumnType.Int;
                    break;
                case DataType.Long:
                    databaseColumnType = DatabaseColumnType.BigInt;
                    break;
                case DataType.Decimal:
                    databaseColumnType = DatabaseColumnType.Decimal;
                    break;
                case DataType.DateTime:
                    databaseColumnType = DatabaseColumnType.DateTime;
                    break;
                case DataType.Timestamp:
                    databaseColumnType = DatabaseColumnType.DateTime;
                    break;
                case DataType.Date:
                    databaseColumnType = DatabaseColumnType.Date;
                    break;
                case DataType.Time:
                    databaseColumnType = DatabaseColumnType.Time;
                    break;
                case DataType.String:
                    databaseColumnType = DatabaseColumnType.Varchar;
                    break;
                case DataType.Record:
                case DataType.List:
                default:
                    databaseColumnType = DatabaseColumnType.Varchar;
                    break;
            }
            return databaseColumnType;
        }

		/// <summary>
		/// 特殊处理类型
		/// </summary>
		/// <param name="dbType"></param>
		/// <param name="datatype"></param>
		/// <param name="length"></param>
		/// <param name="precision"></param>
		/// <returns></returns>
		private static string GetAccurateDataType(DatabaseType dbType, string datatype, int length, int precision)
		{
			if (dbType == DatabaseType.PostgreSQL)
			{
				switch (datatype.ToLower())
				{
					case "decimal": //特殊处理decimal
						datatype += $"({length},{precision})";
						break;
					case "timestamp": //特殊处理时间戳
						datatype += $"{(precision > 0 ? $"({precision})" : "")}";
						break;
					default:    //其余类型都指定列宽与精度
						datatype += $"({length}{(precision > 0 ? "," + precision : "")})";
						break;
				}
			}
			else
			{
				switch (datatype.ToLower())
				{
					case "decimal": //特殊处理decimal
						datatype += $"({length},{precision})";
						break;
					case "timestamp": //特殊处理时间戳
					case "datetime": //特殊处理日期
						if (dbType == DatabaseType.MySQL)   //MySQL默认3精度 不可变
							datatype += $"(3)";
						break;
					default:    //其余类型都指定列宽与精度1
						datatype += $"({length}{(precision > 0 ? "," + precision : "")})";
						break;
				}
			}
			return datatype;
		}
    }
}
